﻿Imports MySql.Data.MySqlClient

Public Class MySqlConnect


    Private mySqlConnection As MySqlConnection

    Private sqlCommand As MySqlCommand


    Public Sub New()
        MyBase.New()
        Dim connectionString = "Server=" & Config.SERVER & ";port=" & Config.PORT & ";"
        connectionString += "Database=" & Config.TABLE_SCHEMA & ";Uid=" & Config.Uid & ";Pwd=" & Config.Pwd & ";"

        Me.mySqlConnection = New MySqlConnection(connectionString)

        Try
            Me.mySqlConnection.Open()
            Me.sqlCommand = New MySqlCommand
            Me.sqlCommand.Connection = Me.mySqlConnection

            Me.mySqlConnection.Close()

        Catch ex As Exception
            MessageBox.Show("Cannot connect to database: ")
        Finally
            Me.mySqlConnection.Dispose()
        End Try

    End Sub


    Public Function executeQuery(ByVal aSql As String) As DataTable
        ''MessageBox.Show("ExecuteQuery: " + aSql)
        Me.mySqlConnection.Open()
        Me.sqlCommand.CommandText = aSql

        Dim dataTable As New DataTable
        Dim dataAdapter As New MySqlDataAdapter(sqlCommand)
        Try
            dataAdapter.Fill(dataTable)
        Catch ex As Exception
        Finally
            Me.mySqlConnection.Close()
            Me.mySqlConnection.Dispose()
        End Try
        Return dataTable
    End Function

    Public Function executeUpdate(ByVal aSql As String) As Boolean

        ''MessageBox.Show("ExecuteUpdate: " + aSql)
        Me.mySqlConnection.Open()
        Me.sqlCommand.CommandText = aSql

        Me.sqlCommand.ExecuteNonQuery()

        Me.mySqlConnection.Close()
        Me.mySqlConnection.Dispose()
    End Function


End Class
